/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sql;

import com.mysql.jdbc.*;
import index.StatsWordElement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Lu
 */
public class SQLInterface {

    private static java.sql.Connection con;
    private static java.sql.Statement statement;

    /**
     * This method will initialize our connection with the database
     */
    public static void init() {
        try {
            loadDriver();
            connectToDatabase();
            statement = con.createStatement();
            System.out.println("SQL interface initialized sucessfully.");
        } catch (ClassNotFoundException e) {
            System.out.println("Impossible to load drivers.");
        } catch (SQLException e) {
            System.out.println("Connection to database failed.");
        }
    }

    /**
     * This method loads mysql drivers.
     *
     * @throws ClassNotFoundException
     */
    public static void loadDriver() throws ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
    }

    /**
     * This method will establish a connection with the database
     */
    public static boolean connectToDatabase() throws SQLException {
        final String url = "jdbc:mysql://localhost/repconndb";
        con = DriverManager.getConnection(url, "root", "");
        return true;
    }

    /**
     * This method will add a word to the database or update its statisics
     */
    public static boolean addFullWord(String shortWord, String fullWord, String document, String element, int position) throws SQLException {
        statement.executeUpdate("INSERT INTO wordposition (document, element, position, word, wholeWord) VALUES ("
                + "(SELECT xpath FROM document WHERE xpath='" + document + "') , "
                + "(SELECT elementXpath FROM element WHERE elementXpath='" + element + "' AND documentXpath='" + document + "') , "
                + "" + position + " , "
                + "(SELECT name FROM word WHERE name='" + shortWord + "') , "
                + "'" + fullWord + "'  );");
        return true;
    }

    public static boolean addWord(String name, int counter, float frequency, float weight) throws SQLException {
        statement.executeUpdate("INSERT INTO word VALUES ('" + name + "', " + counter + ", " + frequency + ", " + weight + ")");
        return true;
    }

    public static boolean addElementType(String elementType) throws SQLException {
        statement.executeUpdate("INSERT INTO elementtype VALUES ('" + elementType + "')");
        return true;
    }

    public static boolean addElement(String elementXPath, String elementType, String documentXPath) throws SQLException {
        statement.executeUpdate("INSERT INTO element (elementXpath, type, documentXpath) VALUES ("
                + "'" + elementXPath + "' , "
                + "(SELECT type FROM elementtype WHERE type='" + elementType + "') , "
                + "(SELECT xpath FROM document WHERE xpath='" + documentXPath + "') );");
        return true;
    }

    public static boolean addDocument(String document) throws SQLException {
        statement.executeUpdate("INSERT INTO document VALUES ('" + document + "')");
        return true;
    }

    public static void addDocuments(ArrayList<ArrayList<StatsWordElement>> statList) {
        String insertStatement = "INSERT INTO statdocelement (document, elementXpath, word, counter, tf, idf) VALUES ";
        for (int i = 0; i < statList.size(); i++) {
            for (StatsWordElement stat : statList.get(i)) {
                insertStatement +=
                        "( "
                        + "(SELECT xpath FROM document WHERE xpath='" + stat.getDocument() + "') , "
                        + "(SELECT elementXpath FROM element WHERE elementXpath='" + stat.getElement() + "' AND documentXpath='" + stat.getDocument() + "') , "
                        + "(SELECT name FROM word WHERE name='" + stat.getWord() + "') , "
                        + "" + stat.getCounter() + "  , "
                        + "" + stat.getTf() + "  , "
                        + "" + stat.getIdf()
                        + " ) ";
            }
            if (i != statList.size() - 1) {
                insertStatement += ",";
            } else {
                insertStatement += ";";
            }
        }
    }

    public static boolean addElementStat(String document, String elementXpath, String word, int counter, float tf, float idf) throws SQLException {
        statement.executeUpdate("INSERT INTO statdocelement (document, elementXpath, word, counter, tf, idf) VALUES ("
                + "(SELECT xpath FROM document WHERE xpath='" + document + "') , "
                + "(SELECT elementXpath FROM element WHERE elementXpath='" + elementXpath + "' AND documentXpath='" + document + "') , "
                + "(SELECT name FROM word WHERE name='" + word + "') , "
                + "" + counter + "  , "
                + "" + tf + "  , "
                + "" + idf + " );");
        return true;
    }

    public static void addElementStats(ArrayList<ArrayList<StatsWordElement>> statList) {
        String insertStatement = "INSERT INTO statdocelement (document, elementXpath, word, counter, tf, idf) VALUES ";
        for (int i = 0; i < statList.size(); i++) {
            for (StatsWordElement stat : statList.get(i)) {
                insertStatement +=
                        "( "
                        + "(SELECT xpath FROM document WHERE xpath='" + stat.getDocument() + "') , "
                        + "(SELECT elementXpath FROM element WHERE elementXpath='" + stat.getElement() + "' AND documentXpath='" + stat.getDocument() + "') , "
                        + "(SELECT name FROM word WHERE name='" + stat.getWord() + "') , "
                        + "" + stat.getCounter() + "  , "
                        + "" + stat.getTf() + "  , "
                        + "" + stat.getIdf()
                        + " ) ";
            }
            if (i != statList.size() - 1) {
                insertStatement += ",";
            } else {
                insertStatement += ";";
            }
        }
    }

    public static boolean cleanDatabase(String[] tables) throws SQLException {
        for (String table : tables) {
            statement.executeUpdate("DELETE FROM " + table + ";");
            System.out.println(table + " cleaned.");
        }
        return true;
    }

    public static java.sql.ResultSet wordQuery(String word){
        try {
            return statement.executeQuery("SELECT word, document, statdocelement.elementXpath, tf, idf, counter, type FROM statdocelement, element WHERE statdocelement.word ='"+word+"' AND element.elementXpath = statdocelement.elementXpath ;");
        } catch (SQLException ex) {
            Logger.getLogger(SQLInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
